#!/usr/bin/env node

/**
 * 应用 RLS 策略脚本
 * 这个脚本用于在现有数据库上应用行级安全策略
 */

const { Pool } = require('pg')
const fs = require('fs')
const path = require('path')

// 数据库连接配置
const pool = new Pool({
    connectionString: process.env.DATABASE_URL || 'postgresql://postgres:postgres123@localhost:5433/product_showcase',
    max: 5,
    idleTimeoutMillis: 30000,
    connectionTimeoutMillis: 2000,
})

async function executeSQL(filePath) {
    const sql = fs.readFileSync(filePath, 'utf8')
    const client = await pool.connect()
    
    try {
        console.log(`执行 SQL 文件: ${filePath}`)
        await client.query(sql)
        console.log(`✓ 成功执行: ${path.basename(filePath)}`)
    } catch (error) {
        console.error(`✗ 执行失败: ${path.basename(filePath)}`)
        console.error('错误详情:', error.message)
        throw error
    } finally {
        client.release()
    }
}

async function checkRLSStatus() {
    const client = await pool.connect()
    
    try {
        console.log('\n检查 RLS 状态...')
        
        // 检查表的 RLS 状态
        const rlsStatus = await client.query(`
            SELECT 
                schemaname,
                tablename,
                rowsecurity as rls_enabled
            FROM pg_tables 
            WHERE tablename IN ('users', 'products')
            AND schemaname = 'public'
        `)
        
        console.log('表的 RLS 状态:')
        rlsStatus.rows.forEach(row => {
            console.log(`  ${row.tablename}: ${row.rls_enabled ? '✓ 已启用' : '✗ 未启用'}`)
        })
        
        // 检查策略数量
        const policies = await client.query(`
            SELECT 
                schemaname,
                tablename,
                policyname,
                cmd,
                qual,
                with_check
            FROM pg_policies 
            WHERE tablename IN ('users', 'products')
            ORDER BY tablename, policyname
        `)
        
        console.log(`\n策略数量: ${policies.rows.length}`)
        policies.rows.forEach(policy => {
            console.log(`  ${policy.tablename}.${policy.policyname} (${policy.cmd})`)
        })
        
        // 检查辅助函数
        const functions = await client.query(`
            SELECT 
                proname,
                prosrc
            FROM pg_proc 
            WHERE proname IN ('current_user_id', 'is_authenticated', 'is_admin')
        `)
        
        console.log(`\n辅助函数: ${functions.rows.length}`)
        functions.rows.forEach(func => {
            console.log(`  ✓ ${func.proname}()`)
        })
        
        // 检查安全视图
        const views = await client.query(`
            SELECT 
                schemaname,
                viewname
            FROM pg_views 
            WHERE viewname = 'users_public'
        `)
        
        console.log(`\n安全视图: ${views.rows.length}`)
        views.rows.forEach(view => {
            console.log(`  ✓ ${view.viewname}`)
        })
        
    } catch (error) {
        console.error('检查 RLS 状态时出错:', error.message)
    } finally {
        client.release()
    }
}

async function testBasicRLS() {
    const client = await pool.connect()
    
    try {
        console.log('\n执行基本 RLS 测试...')
        
        // 测试未认证状态下的查询
        await client.query("SELECT set_config('app.current_user_id', '', false)")
        
        const publicProductsCount = await client.query('SELECT COUNT(*) FROM products')
        console.log(`未认证用户可查看产品数量: ${publicProductsCount.rows[0].count}`)
        
        const publicUsersCount = await client.query('SELECT COUNT(*) FROM users_public')
        console.log(`未认证用户可查看用户数量: ${publicUsersCount.rows[0].count}`)
        
        // 测试认证状态下的查询
        const users = await client.query('SELECT id FROM users LIMIT 1')
        if (users.rows.length > 0) {
            const testUserId = users.rows[0].id
            await client.query("SELECT set_config('app.current_user_id', $1, false)", [testUserId])
            
            const userProducts = await client.query('SELECT COUNT(*) FROM products WHERE user_id = $1', [testUserId])
            console.log(`认证用户可查看自己的产品数量: ${userProducts.rows[0].count}`)
        }
        
        console.log('✓ 基本 RLS 测试完成')
        
    } catch (error) {
        console.error('RLS 测试失败:', error.message)
    } finally {
        client.release()
    }
}

async function main() {
    try {
        console.log('开始应用 RLS 策略...\n')
        
        // 1. 应用 RLS 策略
        const rlsPolicyFile = path.join(__dirname, '../database/init/004_create_rls_policies.sql')
        if (fs.existsSync(rlsPolicyFile)) {
            await executeSQL(rlsPolicyFile)
        } else {
            console.error('RLS 策略文件不存在:', rlsPolicyFile)
            process.exit(1)
        }
        
        // 2. 检查 RLS 状态
        await checkRLSStatus()
        
        // 3. 执行基本测试
        await testBasicRLS()
        
        console.log('\n==================== 应用完成 ====================')
        console.log('RLS 策略已成功应用到数据库')
        console.log('请确保在应用代码中正确设置用户会话')
        console.log('使用以下命令运行完整测试:')
        console.log('  npm run test:rls')
        console.log('====================================================')
        
    } catch (error) {
        console.error('应用 RLS 策略失败:', error.message)
        process.exit(1)
    } finally {
        await pool.end()
    }
}

// 如果直接运行此脚本
if (require.main === module) {
    main().catch(console.error)
}

module.exports = { executeSQL, checkRLSStatus, testBasicRLS }